﻿using System;
using System.Data.SqlClient;
using AccessControl.Common;

namespace AccessControl.DataAcess
{
    public class ManagementReportAccess
    {
        public AccessControlDS.ManagementReportAccessDataTable LoadManagementReportAccess(string PersonRefNumber, DateTime FromDate, DateTime ToDate, int pkWorkflow, int pkEvent, int pkEventType)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var whereClause = "";
                if (!string.IsNullOrEmpty (PersonRefNumber))
                    whereClause += " AND tblPerson.PersonRefNumber = @PersonRefNumber ";
                //if (!string.IsNullOrEmpty (Reason))
                //    whereClause += " AND tblEventLog.Reason = @Reason ";
                if (FromDate != null)
                    whereClause += " AND tblEventLog.DateRegistered > @FromDate ";
                if (ToDate != null)
                    whereClause += " AND tblEventLog.DateRegistered < @ToDate ";
                if (pkWorkflow != 0)
                    whereClause += " AND tblWorkflow.pkWorkflow = @pkWorkflow ";
                if (pkEvent != 0)
                    whereClause += " AND tblEvent.pkEvent = @pkEvent ";
                if (pkEventType != 0)
                    whereClause += " AND tblEventType.pkEventType = @pkEventType ";

                var cmdSelect = new SqlCommand(@"select PersonRefNumber,PersonFirstName,PersonLastName,PersonPhoto,PersonTitle,PersonDesc,Result,
                                                        Reason,DateRegistered,WorkflowName,EventName,EventTypeName
                                                   from tblPerson
                                                  inner join tblEventLog on PersonRefNumber=fkPerson
                                                  inner join tblWorkflow on pkWorkflow=fkWorkflow
                                                  inner join tblEventType on pkEventType=fkEventType
                                                  inner join tblEvent on pkEvent=fkEvent where (1=1) {0}", cn);
                var managementReportAccessTable = new AccessControlDS.ManagementReportAccessDataTable();
                cn.Open();
                var reader = cmdSelect.ExecuteReader();
                if (reader != null)
                    while (reader.Read())
                    {
                        var newRow = managementReportAccessTable.NewManagementReportAccessRow();
                        newRow.PersonRefNumber = (String)reader["PersonRefNumber"];
                        newRow.PersonFirstName = (String)reader["PersonFirstName"];
                        newRow.PersonLastName = (String)reader["PersonLastName"];
                        if (reader["PersonPhoto"] != DBNull.Value)
                            newRow.PersonPhoto = (byte[])reader["PersonPhoto"];
                        newRow.PersonTitle = (Int16)reader["PersonTitle"];
                        newRow.PersonDesc = (String)reader["PersonDesc"];
                        newRow.Result = (Int16)reader["Result"];
                        newRow.Reason = (String)reader["Reason"];
                        newRow.DateRegistered = (DateTime)reader["DateRegistered"];
                        newRow.WorkflowName = (String)reader["WorkflowName"];
                        newRow.EventName = (String)reader["EventName"];
						newRow.EventTypeName = (Int16)reader["EventTypeName"];
                        managementReportAccessTable.AddManagementReportAccessRow(newRow);
                    }
                cn.Close();
                return managementReportAccessTable.Rows.Count != 0 ? managementReportAccessTable : null;
            }
        }
    }
}
